alter table Category
add CategoryDescription varchar(200) not null default('')
go
ALTER PROCEDURE [dbo].[spCategory_Add]
(
	@CategoryName VARCHAR(250),
	@ParentId INT = NULL,
	@IsSingularSelection BIT = 0,
	@IsRoot BIT = 0,
	@SortOrder int = 0,
	@CategoryDescription varchar(200) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	
	IF (@ParentId IS NOT NULL)
	BEGIN
		DECLARE @IsSingularSelectionParent BIT
		
		SELECT @IsSingularSelectionParent = IsSingularSelection
		FROM Category
		WHERE CategoryId = @ParentId
		
		IF (@IsSingularSelectionParent = 1)
			SET @IsSingularSelection = 1
	END	
	
	INSERT INTO Category
	(CategoryName, ParentId, IsSingularSelection, IsRoot, SortOrder, CategoryDescription)
	VALUES (@CategoryName, @ParentId, @IsSingularSelection, @IsRoot, @SortOrder, @CategoryDescription)
END
go
ALTER PROCEDURE [dbo].[spCategory_Update]
(
	@CategoryId int,
	@CategoryName VARCHAR(250),
	@ParentId INT = NULL,
	@IsSingularSelection BIT = 0,
	@IsRoot BIT = 0,
	@SortOrder int = 0,
	@CategoryDescription varchar(200) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	
	IF (@ParentId IS NOT NULL)
	BEGIN
		DECLARE @IsSingularSelectionParent BIT
		
		SELECT @IsSingularSelectionParent = IsSingularSelection
		FROM Category
		WHERE CategoryId = @ParentId
		
		IF (@IsSingularSelectionParent = 1)
			SET @IsSingularSelection = 1
	END	
	

	update Category
	set
		CategoryName = @CategoryName,
		ParentId = @ParentId,
		IsSingularSelection = @IsSingularSelection,
		IsRoot = @IsRoot,
		SortOrder = @SortOrder,
		CategoryDescription = @CategoryDescription
	where CategoryId = @CategoryId
END
go
ALTER PROCEDURE [dbo].[spAPI_Category_GetAll]
	@returnAsDataset bit = 0
AS
BEGIN
	SET NOCOUNT ON
	
	if @returnAsDataset = 0
	begin
		DECLARE @Result XML
		
		SELECT @Result = (SELECT CategoryId AS Id, CategoryName AS Name, CategoryDescription,
							ISNULL(CONVERT(VARCHAR(20), ParentId), '') AS ParentId, 
							IsSingularSelection, IsRoot
							FROM Category (nolock)
							FOR XML AUTO, ROOT('Categories'))

		SELECT ISNULL(@Result, '') AS Result
	end
	else
		SELECT *
		FROM Category (nolock)
END
go
ALTER PROCEDURE [dbo].[spAPI_User_GetAllActivePromotionsByBusiness]
(
	@Token VARCHAR(255),
	@businessId int,
	@Latitude float = null,
	@Longitude float = null,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'

	
	DECLARE @IdResult INT
	
	DECLARE @Result XML
	declare @userId int
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @userId = @userId out
	
	IF @IdResult = 1
	begin
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.Name AS PromotionName,
								Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								0 AS Distance,
								vwBusinessRating.RatingText AS Rating,
								vwBusinessRating.CountReviews AS Reviews,
								15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name, Category.CategoryDescription
									FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day]
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours,
								ISNULL(vwLike.Likes, 0) AS Likes,
								ISNULL(vwLike.Dislikes, 0) AS Dislikes,
								ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
								
								case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
								vwCheckin.LastCheckinDate
							FROM Promotion
								LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
								LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
								LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
								LEFT JOIN (SELECT BusinessId,
											SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
											SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
											CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
										FROM BusinessLike
										GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
								LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
								LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
								LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
								LEFT JOIN (SELECT PromotionCheckin.PromotionId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
											FROM PromotionCheckin
											WHERE PromotionCheckin.UserId = @UserId
											GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							WHERE (Promotion.BusinessId = @BusinessId)
								AND (bb.UserId IS NULL)
								AND (bp.UserId IS NULL)
								/*AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
								--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))	*/
								AND (Promotion.StatusCode = 'ACT')
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
								AND (Business.IsActive = 1)
								AND (NOT EXISTS(SELECT 1 
												FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
												WHERE pc.PromotionId = Promotion.PromotionId))
								AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1)
										
										/*(Promotion.BaseCostTimingId <> 5)
										or
										-- Ongoing, Scheduled timing
										(
											CONVERT(TIME, @Now) BETWEEN CONVERT(TIME, ISNULL(Promotion.BaseCostTimingStartsWithinDay, @BegTime))
											AND CONVERT(TIME, ISNULL(Promotion.BaseCostTimingEndsWithinDay, @EndTime))
										)
									)	*/											
							FOR XML PATH ('Deal'), ROOT('Deals'))
							
		if @returnAsDataset = 1
		begin
			SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
				Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
								
				vwBusinessRating.RatingText AS Rating,
				vwBusinessRating.CountReviews AS Reviews,
																		
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END AS MaxPushes,
									
				isnull(vwPromotionMoney.SummaPush, 0) + ISNULL(vwPromotionMoney.SummaFixedCost, 0) AS SpentMoney,
				ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
				ISNULL(vwPromotionMoney.PromotionRest, 0) as UnusedMoney,
									
				-- Max pushes - Spent Pushes
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
			
				Picture = null,
				PictureContentType = null
			FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
							LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
										FROM PromotionCheckin 
										GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes
										FROM PromotionPush
										GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
							left join vwPromotionMoney on Promotion.PromotionId = vwPromotionMoney.PromotionId
							LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
			WHERE (Promotion.BusinessId = @BusinessId)
				AND (Promotion.StatusCode = 'ACT')
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId))
				AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
							Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1)
				
			RETURN		
		end
	end
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_GetDeals]
(
	@Token VARCHAR(255),
	@Latitude FLOAT,
	@Longtitude FLOAT,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'


	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.Name AS PromotionName,
									Business.BusinessId, Business.BusinessName,
									STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwPromotionRating.RatingText AS PromotionRating,
									vwBusinessRating.CountReviews AS Reviews,
									vwPromotionRating.CountReviews AS PromotionReviews, 
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.Latitude, Business.Longitude,
									Promotion.PromotionText, Business.[Description], 
									
									Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name, Category.CategoryDescription
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes,
									ISNULL(vwLike.Dislikes, 0) AS Dislikes,
									ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
									
									ISNULL(vwLikePromotion.Likes, 0) AS PromotionLikes,
									ISNULL(vwLikePromotion.Dislikes, 0) AS PromotionDislikes,
									ISNULL(vwLikePromotion.IsFeedbackLeft, 0) AS PromotionIsFeedbackLeft,
									
									case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
									vwCheckin.LastCheckinDate
								FROM PromotionPush INNER JOIN Promotion ON PromotionPush.PromotionId = Promotion.PromotionId
									LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
									LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
									LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
									LEFT JOIN vwPromotionRating ON Promotion.PromotionId = vwPromotionRating.PromotionId
									LEFT JOIN (SELECT BusinessId,
												SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
												SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
												CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
											FROM BusinessLike
											GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
									LEFT JOIN (SELECT PromotionId,
												SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
												SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
												CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
											FROM PromotionLike
											GROUP BY PromotionId) AS vwLikePromotion ON Promotion.PromotionId = vwLikePromotion.PromotionId
									LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
									LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
									LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
									LEFT JOIN (SELECT PromotionCheckin.PromotionId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
												FROM PromotionCheckin
												WHERE PromotionCheckin.UserId = @UserId
												GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
								WHERE (PromotionPush.UserId = @UserId)
									AND (bb.UserId IS NULL)
									AND (bp.UserId IS NULL)

									AND (
											((Promotion.ProximityRadiusId not in (12, 13)) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280)))
											-- over 200 miles
											OR ((Promotion.ProximityRadiusId = 13) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) > CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280)))
											-- custom range
											OR ((Promotion.ProximityRadiusId = 12) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, Promotion.CustomRange)))
										)

									AND (Promotion.StatusCode = 'ACT')
									AND (Promotion.Deleted = 0)
									AND (Promotion.Ready = 1)
									AND (Business.IsActive = 1)
									AND (NOT EXISTS(SELECT 1 
													FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
													WHERE pc.PromotionId = Promotion.PromotionId))
									/*AND (EXISTS(SELECT 1 
												FROM PromotionCategory AS pc INNER JOIN fnAPI_UserCategory_All(@UserId) AS uc ON pc.CategoryId = uc.CategoryId
												WHERE pc.PromotionId = Promotion.PromotionId))*/
									AND DATEDIFF(DAY, PromotionPush.DatePush, GETDATE()) <= 180
									AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay, Business.Gmt) = 1)
											
											/*(Promotion.BaseCostTimingId <> 5)
											or
											-- Ongoing, Scheduled timing
											(
												CONVERT(TIME, @Now) BETWEEN CONVERT(TIME, ISNULL(Promotion.BaseCostTimingStartsWithinDay, @BegTime))
												AND CONVERT(TIME, ISNULL(Promotion.BaseCostTimingEndsWithinDay, @EndTime))
											)
										)*/

								ORDER BY PromotionPush.DatePush DESC
								FOR XML PATH ('Deal'), ROOT('Deals'))
		end
		else
		begin
			SELECT
				Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				ProximityRadius.FromRadius as ProximityFromRadius, ProximityRadius.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], BaseCostTiming.Timing as BaseCostTimingName,
				Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
				
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
				
				vwBusinessRating.RatingText AS Rating,
				vwPromotionRating.RatingText AS PromotionRating,
				vwBusinessRating.CountReviews AS Reviews,
				vwPromotionRating.CountReviews AS PromotionReviews, 
				
				CONVERT(INT, 0) AS MaxPushes, 
				CONVERT(MONEY, 0) AS SpentMoney, CONVERT(INT, 0) AS SpentPushes, 
				CONVERT(MONEY, 0) AS UnusedMoney, CONVERT(INT, 0) AS UnusedPushes, 
							
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
				
				Picture = null,
				PictureContentType = null
								
			FROM PromotionPush INNER JOIN Promotion ON PromotionPush.PromotionId = Promotion.PromotionId
				LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
				LEFT JOIN BaseCostTiming ON Promotion.BaseCostTimingId = BaseCostTiming.BaseCostTimingId
				LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
				LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
				LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
				LEFT JOIN (SELECT PromotionCheckin.PromotionId, COUNT(*) AS CheckIns, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
							FROM PromotionCheckin
							WHERE PromotionCheckin.UserId = @UserId
							GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
				LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
				LEFT JOIN vwPromotionRating ON Promotion.PromotionId = vwPromotionRating.PromotionId
				/*
				LEFT JOIN (SELECT BusinessId,
							SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
							SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
							CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
						FROM BusinessLike
						GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
				
				LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
				*/
			WHERE (PromotionPush.UserId = @UserId)
				AND (bb.UserId IS NULL)
				AND (bp.UserId IS NULL)
				/*AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))	*/
				AND (Promotion.StatusCode = 'ACT')
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Business.IsActive = 1)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId))
				/*AND (EXISTS(SELECT 1 
							FROM PromotionCategory AS pc INNER JOIN fnAPI_UserCategory_All(@UserId) AS uc ON pc.CategoryId = uc.CategoryId
							WHERE pc.PromotionId = Promotion.PromotionId))*/
				AND DATEDIFF(DAY, PromotionPush.DatePush, GETDATE()) <= 180
			ORDER BY PromotionPush.DatePush DESc
			
			return
		end
	end

	if @returnAsDataset = 0	
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_Business_Nearby]
(
	@Token VARCHAR(255),
	@Lat FLOAT,
	@Long FLOAT,
	@returnAsDataset bit = 0,
	@searchString varchar(100) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT, @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT top(50) Business.BusinessId, Business.BusinessName,
							STR(dbo.fnCalculateDistance(@Lat, @Long, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.[Description], Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									Business.Latitude, Business.Longitude,
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name, Category.CategoryDescription
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes, 
									ISNULL(vwLike.Dislikes, 0) AS Dislikes, 
									ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
									case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
									vwCheckin.LastCheckinDate
								FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
											LEFT JOIN (SELECT BusinessId,
															SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
															SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
															CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
														FROM BusinessLike
														GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
											LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
											LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
											LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
														FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
														WHERE PromotionCheckin.UserId = @UserId
														GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId
								WHERE (Business.IsActive = 1)
									AND (bb.UserId IS NULL)
									and
									(
										(Business.BusinessName like '%' + @searchString + '%')
										--or (Business.[Description] like '%' + @searchString + '%')
										or exists(select 1 
													from BusinessCategory left join Category on BusinessCategory.CategoryId = Category.CategoryId 
													where (Business.BusinessId = BusinessCategory.BusinessId)
														and (Category.CategoryName like '%' + @searchString + '%'))
									)									
								ORDER BY Distance --ZH 3/24/14
								FOR XML PATH ('Business'), ROOT('Businesses'))
		end
		else
		begin
			SELECT
				Business.BusinessId,
				Business.[Login],
				Business.Email,
				Business.[Password],
				Business.BusinessName, 
				Business.Web,
				Business.BusinessPhone,
				Business.CellPhone,
				Business.TollFreePhone,
				Business.Fax,
				Business.StreetAddress1,
				Business.StreetAddress2, 
				Business.IsActive,
				Business.Deleted, 
				Business.City,
				Business.StateCode,
				Business.ZipCode,
				Business.[Description], 
				Business.CreditCardNo,
				Business.CreditCardExpDate,
				Business.CreditCardName,
				Business.CreditCardPin, 
				Business.BillingAddress1,
				Business.BillingAddress2,
				Business.BillingCity,
				Business.BillingStateCode,
				Business.BillingZipCode,
				Business.BillingUse, 
				Business.Latitude,
				Business.Longitude, 
				CONVERT(VARBINARY(MAX), NULL) AS Picture,
				Business.PictureContentType, 
				Business.ReferencedBy,
				Business.DateAdded,
				Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
				isnull(vwLike.Likes, 0) as Likes,
				isnull(vwLike.Dislikes, 0) as Dislikes,
				vwBusinessRating.Rating,
				vwBusinessRating.CountReviews AS ReviewsCount
			FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
						LEFT JOIN (SELECT BusinessId,
										SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
										SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
										CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
									FROM BusinessLike
									GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
						LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
						LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
						LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
									FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
									WHERE PromotionCheckin.UserId = @UserId
									GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId											
			WHERE (Business.IsActive = 1)
				AND (bb.UserId IS NULL)	
				and
				(
					(Business.BusinessName like '%' + @searchString + '%')
					or (Business.[Description] like '%' + @searchString + '%')
					or exists(select 1 
								from BusinessCategory left join Category on BusinessCategory.CategoryId = Category.CategoryId 
								where (Business.BusinessId = BusinessCategory.BusinessId)
									and (Category.CategoryName like '%' + @searchString + '%'))
				)
						
			return
		end		
	end

	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go
ALTER PROCEDURE [dbo].[spAPI_UserFavorites_GetList]
(
	@Token VARCHAR(255),
	@Latitude FLOAT = null,
	@Longtitude FLOAT = null,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT, @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT Business.BusinessId, Business.BusinessName,
									STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.[Description], Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									Business.Latitude, Business.Longitude,

									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name, Category.CategoryDescription
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes, ISNULL(vwLike.Dislikes, 0) AS Dislikes, ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft
								FROM UserToken INNER JOIN [User] ON UserToken.LoginName = [User].[Login]
											INNER JOIN UserFavorites ON [User].UserId = UserFavorites.UserId
											INNER JOIN Business ON UserFavorites.BusinessId = Business.BusinessId
											LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
											LEFT JOIN (SELECT BusinessId,
															SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
															SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
															CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
														FROM BusinessLike
														GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId						
								WHERE UserToken.SecurityToken = @Token
								FOR XML PATH ('Business'), ROOT('Businesses'))
		end
		else
		begin
			SELECT
				Business.BusinessId,
				Business.[Login],
				Business.Email,
				Business.[Password],
				Business.BusinessName, 
				Business.Web,
				Business.BusinessPhone,
				Business.CellPhone,
				Business.TollFreePhone,
				Business.Fax,
				Business.StreetAddress1,
				Business.StreetAddress2, 
				Business.IsActive,
				Business.Deleted, 
				Business.City,
				Business.StateCode,
				Business.ZipCode,
				Business.[Description], 
				Business.CreditCardNo,
				Business.CreditCardExpDate,
				Business.CreditCardName,
				Business.CreditCardPin, 
				Business.BillingAddress1,
				Business.BillingAddress2,
				Business.BillingCity,
				Business.BillingStateCode,
				Business.BillingZipCode,
				Business.BillingUse, 
				Business.Latitude,
				Business.Longitude, 
				Business.DateAdded,
				CONVERT(VARBINARY(MAX), NULL) AS Picture,
				Business.PictureContentType, 
				Business.ReferencedBy,
				Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
				isnull(vwLike.Likes, 0) as Likes,
				isnull(vwLike.Dislikes, 0) as Dislikes,
								
				vwBusinessRating.Rating,
				vwBusinessRating.CountReviews AS ReviewsCount
			FROM UserToken INNER JOIN [User] ON UserToken.LoginName = [User].[Login]
						INNER JOIN UserFavorites ON [User].UserId = UserFavorites.UserId
						INNER JOIN Business ON UserFavorites.BusinessId = Business.BusinessId
						left join
							(SELECT BusinessId,
								SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
								SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes
							FROM BusinessLike (nolock)
							GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId	
						LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
							
			WHERE UserToken.SecurityToken = @Token	
	
			return
		end		
	end
	
	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
go